#!/bin/bash
# 每半小时 执行
#!/bin/bash

LOG_DIR="/home/cys/www/script/logs"
# 日志文件名包含日期，每次执行的日志前添加时间戳
FILE_NAME="manual_insert_active_one_hour_2"
LOG_FILE="${LOG_DIR}/${FILE_NAME}_$(date +%Y-%m-%d).log"
mkdir -p "${LOG_DIR}"

# 记录开始时间
echo "[$(date +%Y-%m-%d_%H:%M:%S)] 开始执行 clickhouse 命令" >> "${LOG_FILE}"

current_hour=$(date +%H)

for ((i=0; i<=3; i++)); do
  prev_time=$(date -d "${i} hours ago" +"%Y-%m-%d %H")

  # 提取日期（如 2025-11-12）
  prev_date=$(echo "$prev_time" | cut -d' ' -f1)

  # 提取小时数（如 21）
  prev_hour_str=$(echo "$prev_time" | cut -d' ' -f2)

  prev_hour=$((10#$prev_hour_str))

  echo "[$(date +%Y-%m-%d_%H:%M:%S)] 开始执行 日期：${prev_date} 小时数:${prev_hour}" >> "${LOG_FILE}"
clickhouse-client --password Keepgo123@cys -q "
insert into db_cfx.t_data_user_cpa_active_2 (channel, dev_brand, dev_model, os_version,oaid, vn, vc, gaid, maid, activate_time, activate_ip, status)
WITH
    -- 新来的设备
    incoming AS (
     select * from (
      SELECT argMax(channel,base_create_time) as channel,
        argMax(brand,base_create_time) as dev_brand ,
        argMax(model,base_create_time) as dev_model,
        argMax(osv,base_create_time) as os_version,
        uuid,
        argMax(vn,base_create_time) as vn,
        argMax(vc,base_create_time) as vc,
        argMax(gaid,base_create_time) as gaid,
        argMax(maid,base_create_time) as maid,
        min(base_create_time) as activate_time,
        argMax(base_client_ip,base_create_time) as activate_ip,
        argMax(base_status ,base_create_time) as status,
        argMin(lockscreen_setting_state, base_create_time) AS lockscreen_setting_state
      from db_cfx.t_log_base_data_standard_active
      where base_app_id='AnySelectedID'
      and base_create_date='${prev_date}' AND base_create_hour=${prev_hour}
      group by uuid
    ) where vn>'3.37' and lockscreen_setting_state !='false'
  )
SELECT i.channel, i.dev_brand, i.dev_model, i.os_version,i.uuid as oaid, i.vn, i.vc, i.gaid, i.maid, i.activate_time, i.activate_ip, i.status
FROM incoming AS i
ANTI LEFT JOIN (SELECT DISTINCT oaid from db_cfx.t_data_user_cpa_active_2 where activate_date='${prev_date}' and activate_hour=${prev_hour}) AS f
ON i.uuid = f.oaid
ANTI LEFT JOIN (select oaid from db_cfx.t_data_user_cpa_new_deduction where channel_name='lockscreen_false' ) AS d
ON i.uuid = d.oaid;"  2>> "${LOG_FILE}"
sleep 1

clickhouse-client --password Keepgo123@cys -q "
insert into db_cfx.t_data_user_cpa_active_2 (channel, dev_brand, dev_model, os_version,oaid, vn, vc, gaid, maid, activate_time, activate_ip, status)
WITH
    -- 新来的设备
    incoming AS (
    select * from (
     SELECT argMax(channel,base_create_time) as channel,
        argMax(brand,base_create_time) as dev_brand ,
        argMax(model,base_create_time) as dev_model,
        argMax(osv,base_create_time) as os_version,
        uuid,
        argMax(vn,base_create_time) as vn,
        argMax(vc,base_create_time) as vc,
        argMax(gaid,base_create_time) as gaid,
        argMax(maid,base_create_time) as maid,
        min(base_create_time) as activate_time,
        argMax(base_client_ip,base_create_time) as activate_ip,
        argMax(base_status ,base_create_time) as status,
        argMin(lockscreen_setting_state, base_create_time) AS lockscreen_setting_state
      from db_cfx.t_log_base_data_standard_active
      where base_app_id!='AnySelectedID'
      and base_create_date='${prev_date}' AND base_create_hour=${prev_hour}
      group by uuid
      ) where lockscreen_setting_state !='false'
  )
SELECT i.channel, i.dev_brand, i.dev_model, i.os_version,i.uuid as oaid, i.vn, i.vc, i.gaid, i.maid, i.activate_time, i.activate_ip, i.status
FROM incoming AS i
ANTI LEFT JOIN (SELECT DISTINCT oaid from db_cfx.t_data_user_cpa_active_2 where activate_date='${prev_date}' and activate_hour=${prev_hour}) AS f
ON i.uuid = f.oaid
ANTI LEFT JOIN (select oaid from db_cfx.t_data_user_cpa_new_deduction where channel_name='lockscreen_false' ) AS d
ON i.uuid = d.oaid"  2>> "${LOG_FILE}"
sleep 2
done

# 记录结束时间
echo "[$(date +%Y-%m-%d_%H:%M:%S)] shell 脚本执行结束" >> "${LOG_FILE}"
echo "日志已写入 ${LOG_FILE}"